sqLiteConnect <- function(database, table) {
  require(DBI)
  con <- dbConnect(RSQLite::SQLite(), dbname = database)
  query <- dbSendQuery(con, paste("SELECT * FROM ", table, ";", sep="")) 
  result <- fetch(query, n = -1)
  dbClearResult(query)
  dbDisconnect(con)
  return(result)
}

databases <- list()
databases[['bg_facebook']] <- "m5s_fb_beppegrilloit_jan2015.sqlite"


bg_fb_post <- sqLiteConnect(databases[['bg_facebook']], "post")
bg_fb_comment <- sqLiteConnect(databases[['bg_facebook']], "comment")
bg_fb_like <- sqLiteConnect(databases[['bg_facebook']], "post_likes")


require(dplyr)
bg_fb_comment_per_post <-
  bg_fb_comment %>%
  group_by(post_id) %>%
  summarize(n_comments = n())

bg_fb_commenters_per_post <-
  bg_fb_comment %>%
  group_by(post_id) %>%
  summarize(n_commenters = length(unique(from)))

bg_fb_like_per_post <-
  bg_fb_like %>%
  group_by(post_id) %>%
  summarize(n_like = n())

bg_fb_post <- merge(bg_fb_post, bg_fb_comment_per_post, by.x="id", by.y="post_id", all.x=TRUE)
bg_fb_post <- merge(bg_fb_post, bg_fb_commenters_per_post, by.x="id", by.y="post_id", all.x=TRUE)
bg_fb_post <- merge(bg_fb_post, bg_fb_like_per_post, by.x="id", by.y="post_id", all.x=TRUE)

save(bg_fb_post, file = "02_02_m5s_fb_bg_jan15_entire_post_table.RData")

bg_fb_comment$day <- as.Date(bg_fb_comment$created_time)

bg_fb_comment_per_day <-
  bg_fb_comment %>%
  group_by(as.Date(created_time)) %>%
  summarize(comments_on_day = n())

require(data.table)
bg_fb_like <- data.table(bg_fb_like)
bg_fb_post <- data.table(bg_fb_post)

setkey(bg_fb_like, post_id)
setkey(bg_fb_post, id)

bg_fb_post_dateonly <- select(bg_fb_post, id, created_time)
setnames(bg_fb_post_dateonly, "id", "post_id")
setkey(bg_fb_post_dateonly, post_id)

bg_fb_like <- merge(bg_fb_like, bg_fb_post_dateonly)

bg_fb_like_per_day <-
  bg_fb_like %>%
  group_by(as.Date(created_time)) %>%
  summarize(likes_on_day = n())

setnames(bg_fb_like_per_day, "as.Date(created_time)", "day")
setnames(bg_fb_comment_per_day, "as.Date(created_time)", "day")

bg_fb_like_per_day <- bg_fb_like_per_day[order(as.Date(bg_fb_like_per_day$day)),]
bg_fb_comment_per_day <- bg_fb_comment_per_day[order(as.Date(bg_fb_comment_per_day$day)),]

save(bg_fb_like_per_day, bg_fb_comment_per_day, file = "02_02_m5s_fb_fb_jan15_freq_comments_likes.RData")
